package com.smrs.deliver.dao;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.hibernate.Query;

import com.jof.framework.dao.hibernate.BaseDAOHibernateImpl;
import com.jof.framework.util.Pager;
import com.smrs.deliver.model.DeliverHeaderModel;
import com.smrs.deliver.model.DeliverHeaderSearchModel;
import com.smrs.report.model.ReportMonthlyDeliverModel;
import com.smrs.report.model.ReportSearchModel;
import com.smrs.util.DateCommonUtil;


public class DeliverHeaderDao extends BaseDAOHibernateImpl<DeliverHeaderModel, Long>{
	
	@SuppressWarnings("unchecked")
	public Pager<DeliverHeaderModel> findDeliverHeader(DeliverHeaderSearchModel deliverHeaderSearch, Pager<DeliverHeaderModel> pager){
		String queryString = " from DeliverHeaderModel where 1=1 ";
		ArrayList<Object> list = new ArrayList<Object>(); 
		if(deliverHeaderSearch.getStatus()!=null){
			queryString = queryString+" and status=?";
			list.add(deliverHeaderSearch.getStatus());
		}
		if(deliverHeaderSearch.getStartDate() !=null){
			queryString= queryString + " and creationDate >=? ";
			Date mixOfDate = DateCommonUtil.getBeginOfDate(deliverHeaderSearch.getStartDate());
			list.add(mixOfDate);			
		}
		if(deliverHeaderSearch.getEndDate()!=null){
			queryString= queryString + " and creationDate <? ";
			Date endOfDate = DateCommonUtil.getEndOfDate(deliverHeaderSearch.getEndDate());
			list.add(endOfDate);			
		}
		if(deliverHeaderSearch.getStoreId()!=null){
			queryString= queryString + " and storeId =? ";
			list.add(deliverHeaderSearch.getStoreId());	
		}
		if(StringUtils.isNotEmpty(deliverHeaderSearch.getStoreCode())){
			queryString= queryString + " and storeCode like  '%"+deliverHeaderSearch.getStoreCode()+"%'";
		}
		if(StringUtils.isNotEmpty(deliverHeaderSearch.getStoreName())){
			queryString= queryString + " and storeName like  '%"+deliverHeaderSearch.getStoreName()+"%'";
		}
		
		Query query = this.createHQLQuery(queryString, list.toArray());
		List resultList = query.list();
		long totalRecords = this.countHQLResult(queryString, list.toArray());
		pager = Pager.cloneFromPager(pager, totalRecords, resultList);
		return pager;
	}	
	
	public Pager<ReportMonthlyDeliverModel> getDeliverReportOfMonthly(ReportSearchModel searchModel,Pager<ReportMonthlyDeliverModel> pager){
		//String queryString = "select storeId,storeName,storeCode,sum(actualAmount),sum(actualQuantify),DATE_FORMAT(creationDate,'%Y-%m') " +
		//		" from DeliverHeaderModel where 1=1 ";
		//Connection conn = this.sessionFactory.getCurrentSession().connection();
		//conn.createStatement();		
		String sql = " select store_Id,store_Name,store_Code,sum(actual_Amount),sum(actual_Quantify),DATE_FORMAT(Deliver_Header.creation_Date,'%Y-%m') "+
				" from Deliver_Header  #jointable# where 1=1 ";;
		ArrayList<Object> list = new ArrayList<Object>(); 
		String joinsql = " ";
		if(searchModel.getRegion()!=null && searchModel.getRegion().getId()!=null){
		 joinsql = " inner join store  on store.id = Deliver_Header.store_id and store.region_id="+searchModel.getRegion().getId();
		}
		//sql.replaceAll("#join#", joinsql);
		sql=sql.replace("#jointable#", joinsql);
		if(searchModel.getStartDate()!=null){			
			//queryString= queryString + " and creationDate >=? ";
			Date mixOfDate = DateCommonUtil.getFirstDateOfMonth(searchModel.getStartDate());
			list.add(mixOfDate);
			sql = sql + " and Deliver_Header.creation_Date >= '" + DateFormatUtils.format(mixOfDate, "yyyy-MM-dd") +"'";
		}
		if(searchModel.getEndDate()!=null){
			//queryString= queryString + " and creationDate <? ";			
			Date endOfDate = DateCommonUtil.getLastDateOfMonth(searchModel.getEndDate());
			sql = sql + " and Deliver_Header.creation_Date < '" + DateFormatUtils.format(endOfDate, "yyyy-MM-dd")+"'";
			list.add(endOfDate);		
			
		}
		//queryString= queryString+" group by storeId,storeName,DATE_FORMAT(creationDate,'%Y-%m') ";
		if(searchModel.getStoreId()!=null){
			sql = sql + "  and store_id =" + searchModel.getStoreId();
		}
		if(StringUtils.isNotEmpty(searchModel.getStoreCode())){
			sql = sql + " and store_code like '%"+searchModel.getStoreCode()+"%'";
		}
		if(StringUtils.isNotEmpty(searchModel.getStoreName())){
			sql = sql + " and store_code like '%"+searchModel.getStoreName()+"%'";
		}
		
		
		sql = sql  +" group by store_Id,store_Name,DATE_FORMAT(creation_Date,'%Y-%m') ";
		
		
		
		Query query = this.createSQLQuery(sql, new Object[]{});
		this.setPageParameterToQuery(query, pager);
		List tempResultList = query.list();
		List<ReportMonthlyDeliverModel> resultList = new ArrayList<ReportMonthlyDeliverModel>();
		if(CollectionUtils.isNotEmpty(tempResultList)){
			for(int index=0;index<tempResultList.size();index++){
				Object[] values =(Object[])tempResultList.get(index);
				ReportMonthlyDeliverModel reportModel = new ReportMonthlyDeliverModel();
				reportModel.setStoreId(Long.parseLong(ObjectUtils.toString(values[0],"0")));
				reportModel.setStoreName(ObjectUtils.toString(values[1]));
				reportModel.setStoreCode(ObjectUtils.toString(values[2]));
				reportModel.setActualAmount(Double.valueOf(ObjectUtils.toString(values[3],"0")));
				reportModel.setActualQuantify(Double.valueOf(ObjectUtils.toString(values[4],"0")));
				reportModel.setReportDate(ObjectUtils.toString(values[5]));
				resultList.add(reportModel);
			}
		}
		//long totalRecords = this.countHQLResult(queryString, list.toArray());
		String countSQL = "select count(*) from (" +sql +") d";
		//Query queryCount = this.createHQLQuery(countSQL,new Object[]{});
		Query queryCount = this.createSQLQuery(countSQL,new Object[]{});
		List countList = queryCount.list();
		Object obj = countList.get(0);
		Long totalRecords = 0l;
		pager = Pager.cloneFromPager(pager, totalRecords, resultList);
		return pager;
	}
	
}
